﻿
'Description:入库信息类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Wangjh
'create Date:2017-4-15
'Rewriter:
'Rewrite Date:
Imports System.Text
Imports DatabaseAccess.DBUtility
Imports System.Data.OracleClient
Imports BusinessRule.DTO

Public Class uLMSContainerInStoreBusiness

    Implements IBusiness.uLMSContainerInStoreIBusiness
    ''' <summary>
    ''' 写入入库信息数据到入库信息表中
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertContainerInStoreData(ByVal para As Dictionary(Of String, Object)) As Boolean Implements IBusiness.uLMSContainerInStoreIBusiness.InsertContainerInStoreData
        InsertContainerInStoreData = False
        Dim strsql As New StringBuilder
        '将之前同批次的入库信息设置为无效 add tianFW 2018年4月18日
        strsql.AppendFormat("UPDATE CONTAINERINSTORE CI SET CI.ISUSE = 1 WHERE CI.CHARG='{0}'", para("Batch"))
        DatabaseHelper.ExecuteSql(strsql.ToString())
        strsql.Clear()
        '
        strsql.AppendLine("insert into CONTAINERINSTORE(STYPE,AUFNR,MATNR,MAKTX,ZJX,TJMNG," &
                         " ZHGSL,BFSL,ZYCHGSL,CHARG,ZZBDSL,ZDLDH,ZBHGDH,ZWTDW,FEVOR," &
                        " ZBZ,ZLX,ZGCSC,ZFJSC,ZJYSC,ZTJSC,ZCNSJ,ZGCSJ,ZFJSJ,ZJYSJ," &
                       " ZCT,ZWTMS,EmployeeID,CreateDate,IsUse,IsIntegration,ISTRANSFER,CONTAINERINSTOREID,ZPCTC,ZLPNUM,LGORT,workflowid,IsArmyCheck,OilSealDate)")
        strsql.AppendLine("values (:StoreInType,:ProductOrderNo,:PartNo,:PartName,:MachineType,:SubmitNum,:QualifiedNum" &
                          ",:LossNum,:OnceQualifiedNum,:Batch,:QualityPolicyNumber,:ReplaceMaterialNumber,:UnqualifiedApproveNo,:EntrustUnit,:BranchFactory" &
                          ",:Notes,:Type,:FactoryTryLatheNum,:AppendTryLatheNum,:TestTryLatheNum,:SubmitTryLatheNum,:InFactoryWorkTime,:FactoryTryLatheTime" &
                          ",:AppendTryLatheTime,:TestTryLatheTime,:LathePlatform,:ProblemDesc,:EmployeeId,sysDate,0,1,0,:CONTAINERINSTOREID,:ZPCTC,:ZLPNUM,:LGORT,:workflowid,:IsArmyCheck,:OilSealDate)")

        Dim insertParam(34) As OracleParameter
        'modify tianFW 2017/10/10 将插入语句简化
        insertParam(0) = DatabaseHelper.NewParameter(":StoreInType", para("StoreInType"), OracleType.VarChar)
        insertParam(1) = DatabaseHelper.NewParameter(":ProductOrderNo", para("ProductOrderNo"), OracleType.VarChar)
        insertParam(2) = DatabaseHelper.NewParameter(":PartNo", para("PartNo"), OracleType.VarChar)
        insertParam(3) = DatabaseHelper.NewParameter(":PartName", para("PartName"), OracleType.VarChar)
        insertParam(4) = DatabaseHelper.NewParameter(":MachineType", para("MachineType"), OracleType.VarChar)
        insertParam(5) = DatabaseHelper.NewParameter(":SubmitNum", para("SubmitNum"), OracleType.Number)
        insertParam(6) = DatabaseHelper.NewParameter(":QualifiedNum", para("QualifiedNum"), OracleType.Number)
        insertParam(7) = DatabaseHelper.NewParameter(":LossNum", para("LossNum"), OracleType.Number)
        insertParam(8) = DatabaseHelper.NewParameter(":OnceQualifiedNum", para("OnceQualifiedNum"), OracleType.Number)
        insertParam(9) = DatabaseHelper.NewParameter(":Batch", para("Batch"), OracleType.VarChar)
        insertParam(10) = DatabaseHelper.NewParameter(":QualityPolicyNumber", para("QualityPolicyNumber"), OracleType.Number)
        insertParam(11) = DatabaseHelper.NewParameter(":ReplaceMaterialNumber", para("ReplaceMaterialNumber"), OracleType.VarChar)
        insertParam(12) = DatabaseHelper.NewParameter(":UnqualifiedApproveNo", para("UnqualifiedApproveNo"), OracleType.VarChar)
        insertParam(13) = DatabaseHelper.NewParameter(":EntrustUnit", para("EntrustUnit"), OracleType.VarChar)
        insertParam(14) = DatabaseHelper.NewParameter(":BranchFactory", para("BranchFactory"), OracleType.VarChar)
        insertParam(15) = DatabaseHelper.NewParameter(":Notes", para("Notes"), OracleType.VarChar)
        insertParam(16) = DatabaseHelper.NewParameter(":Type", para("Type"), OracleType.VarChar)
        insertParam(17) = DatabaseHelper.NewParameter(":FactoryTryLatheNum", para("FactoryTryLatheNum"), OracleType.VarChar)
        insertParam(18) = DatabaseHelper.NewParameter(":AppendTryLatheNum", para("AppendTryLatheNum"), OracleType.VarChar)
        insertParam(19) = DatabaseHelper.NewParameter(":TestTryLatheNum", para("TestTryLatheNum"), OracleType.VarChar)
        insertParam(20) = DatabaseHelper.NewParameter(":SubmitTryLatheNum", para("SubmitTryLatheNum"), OracleType.VarChar)
        insertParam(21) = DatabaseHelper.NewParameter(":InFactoryWorkTime", para("InFactoryWorkTime"), OracleType.VarChar)
        insertParam(22) = DatabaseHelper.NewParameter(":FactoryTryLatheTime", para("FactoryTryLatheTime"), OracleType.VarChar)
        insertParam(23) = DatabaseHelper.NewParameter(":AppendTryLatheTime", para("AppendTryLatheTime"), OracleType.VarChar)
        insertParam(24) = DatabaseHelper.NewParameter(":TestTryLatheTime", para("TestTryLatheTime"), OracleType.VarChar)
        insertParam(25) = DatabaseHelper.NewParameter(":LathePlatform", para("LathePlatform"), OracleType.VarChar)
        insertParam(26) = DatabaseHelper.NewParameter(":ProblemDesc", para("ProblemDesc"), OracleType.VarChar)
        insertParam(27) = DatabaseHelper.NewParameter(":EmployeeId", para("EmployeeId"), OracleType.VarChar)
        Dim ID As String = Guid.NewGuid().ToString 'modify tianFW 2018年3月16日 将唯一ID提出来
        insertParam(28) = DatabaseHelper.NewParameter(":CONTAINERINSTOREID", ID, OracleType.VarChar)
        'add YangJiang 20180516 添加批次投产数量、炉批顺序号、库存地点
        insertParam(29) = DatabaseHelper.NewParameter(":ZPCTC", para("ZPCTC"), OracleType.VarChar)
        insertParam(30) = DatabaseHelper.NewParameter(":ZLPNUM", para("ZLPNUM"), OracleType.VarChar)
        insertParam(31) = DatabaseHelper.NewParameter(":LGORT", para("LGORT"), OracleType.VarChar)
        insertParam(32) = DatabaseHelper.NewParameter(":workflowid", para("WorkflowID"), OracleType.VarChar)

        insertParam(33) = DatabaseHelper.NewParameter(":IsArmyCheck", para("IsArmyCheck"), OracleType.Number)
        If para("OilSealDate").ToString <> "" Then
            insertParam(34) = DatabaseHelper.NewParameter(":OilSealDate", para("OilSealDate"), OracleType.DateTime)
        Else
            insertParam(34) = DatabaseHelper.NewParameter(":OilSealDate", para("OilSealDate"), OracleType.VarChar)
        End If

        If DatabaseHelper.ExecuteSql(strsql.ToString, insertParam) > 0 Then
            InsertContainerInStoreData = True
        End If
        '存储去向详细信息 add tianFW 2018年3月16日
        Dim Detaildt As DataTable = para("Detaildt")
        If Detaildt.Rows.Count = 0 Then
            InsertContainerInStoreData = True
            Exit Function
        End If
        If Not AddInStroeDetail(Detaildt, para, ID) Then
            InsertContainerInStoreData = False
            Exit Function
        End If
        InsertContainerInStoreData = True
    End Function

    ''' <summary>
    ''' 添加入库去向明细信息 create tianFW 2018年4月3日
    ''' </summary>
    ''' <param name="Detaildt"></param>
    ''' <param name="para"></param>
    ''' <param name="ID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddInStroeDetail(ByVal Detaildt As DataTable, ByVal para As Dictionary(Of String, Object), ByVal ID As String) As Boolean Implements IBusiness.uLMSContainerInStoreIBusiness.AddInStroeDetail
        AddInStroeDetail = False
        If para.Keys.Contains("separateUpdate") Then
            Dim strsql As New StringBuilder
            For Each dr As DataRow In Detaildt.Rows
                strsql.Clear()
                strsql.AppendFormat("UPDATE CONTAINERINSTOREDETAIL cd SET cd.tofactoryid='{0}',cd.productfamilyid='{1}', ", dr("FactoryID"), dr("ProductFamilyIDs"))
                strsql.AppendFormat("cd.toqty={0},cd.employeeid='{1}' WHERE cd.containerinstoredetailid='{2}'", dr("Qty"), para("EmployeeId"), dr("ID"))
                DatabaseHelper.ExecuteSql(strsql.ToString())
            Next
            AddInStroeDetail = True
        Else
            Dim insertParam_(5) As OracleParameter
            Dim newID As String = "" '唯一ID
            Dim strsql As New StringBuilder
            If para.Keys.Contains("Change") Then
                strsql.AppendFormat("DELETE FROM CONTAINERINSTOREDETAIL CID WHERE CID.CONTAINERINSTOREID='{0}'", ID)
                DatabaseHelper.ExecuteSql(strsql.ToString())
                strsql.Clear()
                strsql.AppendFormat("DELETE FROM CONTAINERINSTOREDETAILPARTNO CIDP WHERE CIDP.CONTAINERID='{0}'", para("ContainerID"))
                DatabaseHelper.ExecuteSql(strsql.ToString())
            End If
            For Each dr As DataRow In Detaildt.Rows
                Array.Clear(insertParam_, 0, 5)
                strsql.Clear()
                strsql.AppendLine("INSERT INTO CONTAINERINSTOREDETAIL(CONTAINERINSTOREID,CONTAINERINSTOREDETAILID,TOFACTORYID,PRODUCTFAMILYID,TOQTY,EMPLOYEEID,CREATEDATE)")
                strsql.AppendLine("VALUES(:CONTAINERINSTOREID,:CONTAINERINSTOREDETAILID,:TOFACTORYID,:PRODUCTFAMILYID,:TOQTY,:EMPLOYEEID,SYSDATE)")
                insertParam_(0) = DatabaseHelper.NewParameter(":CONTAINERINSTOREID", ID, OracleType.VarChar)
                newID = Guid.NewGuid().ToString
                insertParam_(1) = DatabaseHelper.NewParameter(":CONTAINERINSTOREDETAILID", newID, OracleType.VarChar)
                insertParam_(2) = DatabaseHelper.NewParameter(":TOFACTORYID", dr("FactoryID"), OracleType.VarChar)
                insertParam_(3) = DatabaseHelper.NewParameter(":PRODUCTFAMILYID", dr("ProductFamilyIDs"), OracleType.VarChar)
                insertParam_(4) = DatabaseHelper.NewParameter(":TOQTY", dr("Qty"), OracleType.Number)
                insertParam_(5) = DatabaseHelper.NewParameter(":EMPLOYEEID", para("EmployeeId"), OracleType.VarChar)
                DatabaseHelper.ExecuteSql(strsql.ToString, insertParam_)
                strsql.Clear()
                strsql.AppendLine("INSERT INTO CONTAINERINSTOREDETAILPARTNO(CONTAINERID,CONTAINERINSTOREDETAILID)")
                strsql.AppendFormat("VALUES('{0}','{1}')", para("ContainerID"), newID)
                If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
                    AddInStroeDetail = True
                Else
                    AddInStroeDetail = False
                End If
            Next
        End If
    End Function

    ''' <summary>
    ''' 获取批次成品检验信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetFinishedCheckInfo(ByVal containerID As String) As DataTable Implements IBusiness.uLMSContainerInStoreIBusiness.GetFinishedCheckInfo
        Dim strSql As String = " SELECT c.mfgbatch,SUM(CFCI.CHECKQTY) ALLCHECKQTY,SUM(CFCI.ACCEPTANCEQTY) ALLACCEPTANCEQTY,SUM(CFCI.LOSSQTY) ALLLOSSQTY FROM CONTAINERFINISHEDCHECKINFO CFCI"
        strSql += " left join container c on c.containerid=cfci.containerid"
        strSql += String.Format(" WHERE CFCI.CONTAINERID = '{0}'", containerID)
        strSql += " GROUP BY c.mfgbatch"
        GetFinishedCheckInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取已经成检但未入库的记录
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Protected Function GetNotInStoreOfChecked(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSContainerInStoreIBusiness.GetNotInStoreOfChecked
        Dim condition As String = String.Format(" and cfci.factoryid='{0}'", para("FactoryID"))
        If para.Keys.Contains("MfgOrderName") Then
            condition += String.Format(" and m.mfgordername like '%{0}%'", para("MfgOrderName"))
        End If
        If para.Keys.Contains("ContainerName") Then
            condition += String.Format(" and c.ContainerName like '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("ProductName") Then
            condition += String.Format(" and pb.ProductName like '%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("SpecNO") Then
            condition += String.Format(" and s.SpecNO like '%{0}%'", para("SpecNO"))
        End If


        Dim strSql As String = <string>
                                 SELECT m.mfgorderid,c.mfgbatch,null containerid,p.description,c.qty,s.specno,m.mfgordername,null containername,
                                c.togetherflag,pb.productname,ws.workflowstepname,ws.workflowstepid,ws.workflowid,ws.sequence,c.containertype,
                                DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,cfci.acceptanceqty,cfci.lossqty,
                                cfci.defectqty,cfci.reworkqty,cfci.createdate ,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,
                                 (cci.productname||'/'||cci.containerno) ProductBatchHidden     FROM containerfinishedcheckinfo cfci 
                                LEFT JOIN container c ON c.containerid=cfci.containerid
                                LEFT JOIN containercurrentinfo cci ON cci.containerid=c.containerid
                                LEFT JOIN containerinstore cis ON cis.charg=c.containername AND cis.isuse = 0
                                LEFT JOIN workflowstep ws ON ws.workflowstepid=cfci.workflowstepid
                                LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid
                                LEFT JOIN product p ON p.productid=c.productid
                                LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                                LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                                LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                                LEFT JOIN containerfinishedcheckinfo cfci2 ON cfci2.containerid=cfci.containerid
                                WHERE cfci.isuse=0 AND cis.charg IS NULL AND c.status=1 AND c.containertype!='7'
                               </string>
        strSql += condition
        strSql += " GROUP BY m.mfgorderid,c.mfgbatch,p.description,c.qty,s.specno,m.mfgordername,  c.togetherflag,pb.productname,ws.workflowstepname,ws.workflowstepid,ws.workflowid,ws.sequence,c.containertype,cfci.acceptanceqty,cfci.lossqty,cfci.defectqty,cfci.reworkqty,cfci.createdate,cci.containerno,cci.workflowroute,cci.productname"
        strSql += " HAVING cfci.createdate>=MAX(cfci2.createdate)"
        'Dim paraPage As New uMESPagingDataDTO
        'paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        'paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        'paraPage.strSQL = strSql
        'GetNotInStoreOfChecked = DatabaseHelper.RunProcedureForPage(paraPage)
        strSql += <string>
                UNION ALL
                SELECT m.mfgorderid,NULL mfgbatch, NULL containerid,p.description,sum(c.qty) qty,s.specno,m.mfgordername,NULL containername,
            c.togetherflag,pb.productname,ws.workflowstepname,
            ws.workflowstepid,ws.workflowid,ws.sequence,c.containertype,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,
            sum(cfci.acceptanceqty) acceptanceqty,sum(cfci.lossqty) lossqty,sum(cfci.defectqty) defectqty,sum(cfci.reworkqty) reworkqty,
            NULL createdate,(nvl(cci.workflowroute,cci.productname)||'/'||cci.containerno) ProductBatch,(cci.productname||'/'||cci.containerno) ProductBatchHidden FROM containerfinishedcheckinfo cfci 
                LEFT JOIN container c ON c.containerid=cfci.containerid
                LEFT JOIN containercurrentinfo cci ON cci.containerid=c.containerid
                LEFT JOIN containerinstore cis ON cis.charg=c.containername AND cis.isuse = 0
                LEFT JOIN workflowstep ws ON ws.workflowstepid=cfci.workflowstepid
                LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid
                LEFT JOIN product p ON p.productid=c.productid
                LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid
                LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
                LEFT JOIN 
                ( SELECT cfci.containerid,MAX(cfci.createdate) maxcreatedate FROM containerfinishedcheckinfo cfci WHERE cfci.isuse=0 GROUP BY cfci.containerid) a ON
                a.containerid=cfci.containerid
                WHERE cfci.isuse=0 AND cfci.createdate=a.maxcreatedate AND cis.charg IS NULL AND c.status=1 AND c.containertype='7'
                </string>.Value
        strSql += condition
        strSql += <string>
                      GROUP BY m.mfgorderid,p.description,c.qty,s.specno,m.mfgordername,c.togetherflag,pb.productname,ws.workflowstepname,ws.workflowstepid,ws.workflowid,ws.sequence,c.containertype,cfci.acceptanceqty,cfci.lossqty,cfci.defectqty,cfci.reworkqty,c.togetherflag,cci.containerno,cci.workflowroute,cci.productname
                  </string>.Value

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        GetNotInStoreOfChecked = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
    ''' <summary>
    ''' 查询入库子卡
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetChildContainerFinishCheck(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSContainerInStoreIBusiness.GetChildContainerFinishCheck
        Dim strSql As String = <string>
                            SELECT c.containerid,c.containername,c.mfgbatch,cfci.createdate,e.fullname FROM containerfinishedcheckinfo cfci
							LEFT JOIN container c ON c.containerid=cfci.containerid
                            LEFT JOIN containerinstore cis ON cis.charg=c.containername AND cis.isuse = 0
                             LEFT JOIN 
                ( SELECT cfci.containerid,MAX(cfci.createdate) maxcreatedate FROM containerfinishedcheckinfo cfci WHERE cfci.isuse=0 GROUP BY cfci.containerid) a ON
                a.containerid=cfci.containerid
                            left join employee e on e.employeeid=cfci.checkerid
							WHERE cfci.isuse=0 and cis.charg IS NULL AND c.status=1 AND cis.charg IS NULL and cfci.createdate=a.maxcreatedate 
                               </string>
        strSql += String.Format(" AND cfci.workflowstepid='{0}' AND c.togetherflag='{1}' and c.containertype=7 AND cfci.factoryid='{2}'", para("WorkFlowStepID"), para("TogetherFlag"), para("FactoryID"))
        If para.ContainsKey("MfgorderId") Then
            strSql += String.Format(" and c.MfgorderId='{0}' ", para("MfgorderId"))
        End If
        strSql += <string>
                      UNION
                         SELECT c.containerid,c.containername,c.mfgbatch,cfci.createdate,e.fullname FROM containerfinishedcheckinfo cfci
							LEFT JOIN container c ON c.containerid=cfci.containerid
                            LEFT JOIN containerinstore cis ON cis.charg=c.containername AND cis.isuse = 0
                             LEFT JOIN 
                ( SELECT cfci.containerid,MAX(cfci.createdate) maxcreatedate FROM containerfinishedcheckinfo cfci WHERE cfci.isuse=0 GROUP BY cfci.containerid) a ON
                a.containerid=cfci.containerid
                             left join employee e on e.employeeid=cfci.checkerid
							WHERE cfci.isuse=0 and cis.charg IS NULL AND c.status=1 AND cis.charg IS NULL and cfci.createdate=a.maxcreatedate 
                  </string>.Value
        strSql += String.Format(" AND cfci.workflowstepid='{0}' AND c.containername='{1}' and c.containertype!=7 AND cfci.factoryid='{2}'", para("WorkFlowStepID"), para("ProductBatchHidden"), para("FactoryID"))
        If para.ContainsKey("MfgorderId") Then
            strSql += String.Format(" and c.MfgorderId='{0}' ", para("MfgorderId"))
        End If

        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取入库批次信息数据
    ''' create:tianFW 
    ''' </summary>
    ''' <param name="QueryCondition"></param>
    ''' <returns></returns>
    ''' <remarks>REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME, modify by tianFW 2018年5月17日 单件单卡取C.TOGETHERFLAG 数量批取C.PRODUCTNAME</remarks>
    Public Function GetInstroeContainerInfo(ByVal QueryCondition As System.Collections.Generic.Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSContainerInStoreIBusiness.GetInstroeContainerInfo
        Dim strSelect As String =
          <string>
             SELECT NVL(C.WORKFLOWROUTE,C.PRODUCTNAME) PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.TOGETHERFLAG,C.CONTAINERTYPE,
            DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.FACTORYID,C.PRODUCTDESCRIPTION,
            C.PRODUCTREVISION,NVL(C.WORKFLOWROUTE,C.PRODUCTNAME)||'/'||REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,
            (c.productname||'/'||c.containerno) ProductBatch,
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,'' FaContianerID,NULL TJMNG,NULL ZYCHGSL,NULL ZHGSL,'' CONTAINERINSTOREID,NULL CREATEDATE")
        Dim sbCondition As New StringBuilder
        sbCondition.AppendLine("FROM CONTAINERINSTORE CI")
        sbCondition.AppendLine("INNER JOIN CONTAINERCURRENTINFO C ON C.CONTAINERNAME = CI.CHARG")
        sbCondition.AppendLine("LEFT JOIN  (SELECT cid.containerinstoreid FROM containerinstoredetail cid GROUP BY cid.containerinstoreid) ")
        sbCondition.AppendLine("K ON K.containerinstoreid = ci.containerinstoreid")
        sbCondition.AppendLine("WHERE C.SPLITNUM=0 AND C.CONTAINERSTATUS=1 AND CI.ISUSE=0")
        If QueryCondition("Status") = "0" Then
            sbCondition.AppendLine(" AND k.containerinstoreid IS NULL")
        Else
            sbCondition.AppendLine(" AND k.containerinstoreid IS NOT NULL")
        End If
        sbCondition.AppendFormat("AND C.Originalfactoryid='{0}'", QueryCondition("FactoryID"))
        If QueryCondition.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat("AND C.CONTAINERNAME LIKE '%{0}%'", QueryCondition("ContainerName"))
        End If
        If QueryCondition.Keys.Contains("mfgOrderName") Then
            sbCondition.AppendFormat("AND C.MFGORDERNAME LIKE '%{0}%'", QueryCondition("mfgOrderName"))
        End If
        sb.AppendLine(sbCondition.ToString())
        sb.AppendLine(" AND C.CONTAINERTYPE='7'")
        sb.AppendLine("GROUP BY C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.TOGETHERFLAG,C.TOGETHERFLAG,C.CONTAINERTYPE,C.MFGORDERNAME,")
        sb.AppendLine("  C.MFGORDERNAME,C.FACTORYID,C.PRODUCTDESCRIPTION,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,C.WORKFLOWROUTE,c.containerno")
        sb.AppendLine(" UNION ")
        sb.AppendLine(strSelect)
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.CONTAINERID AS FaContianerID,CI.TJMNG,CI.ZYCHGSL,CI.ZHGSL,CI.CONTAINERINSTOREID,CI.CREATEDATE")
        sb.AppendLine(sbCondition.ToString())
        sb.AppendLine(" AND C.CONTAINERTYPE<>'7' ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(QueryCondition("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(QueryCondition("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Dim sbCondition_ As String = String.Format("SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.TOGETHERFLAG, " &
                                                   "C.CONTAINERTYPE,C.MFGORDERNAME,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,C.QTY AS WIPQTY , " &
                                                   "C.CONTAINERID,C.CONTAINERNAME,CI.TJMNG,CI.ZYCHGSL,CI.ZHGSL,CI.CONTAINERINSTOREID,CI.CREATEDATE {0}", sbCondition)
        Return DatabaseHelper.GetContainerForPage(paraPage, sbCondition_)
    End Function

    ''' <summary>
    ''' 获取入库明细信息 create tianFW 2018年4月3日
    ''' </summary>
    ''' <param name="containerInstroeID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetInstroeDetaliInfo(ByVal containerInstroeID As String) As DataTable Implements IBusiness.uLMSContainerInStoreIBusiness.GetInstroeDetaliInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CID.*,pf.productfamilyname FROM CONTAINERINSTOREDETAIL CID  ")
        strsql.AppendLine("LEFT JOIN productfamily PF ON PF.Productfamilyid = cid.productfamilyid")
        strsql.AppendFormat("WHERE CID.CONTAINERINSTOREID in ({0})", containerInstroeID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function
    ''' <summary>
    ''' 获取批次所有报废的工序信息
    ''' add:Wangjh 20180425
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerScrapInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSContainerInStoreIBusiness.GetContainerScrapInfo
        Dim strSql = "SELECT c.containername,c.containerid,ws.workflowstepname,ws.workflowstepid,qd.qty FROM qtyhistorydetails qd " &
                    " LEFT JOIN historymainline hml ON hml.txnid=qd.txnid" &
                    " LEFT JOIN container c ON c.containerid=qd.containerid" &
                    " LEFT JOIN workflowstep ws ON ws.workflowstepid=hml.workflowstepid " &
                    " WHERE qd.changeqtytype = 2"
        strSql += String.Format(" AND qd.containerid='{0}'", para("ContainerID"))
        If para.ContainsKey("WorkFlowID") Then
            strSql += String.Format(" AND ws.workflowid='{0}'", para("WorkFlowID"))
        End If
        If para.ContainsKey("Sequence") Then
            strSql += String.Format(" AND ws.sequence<={0}", para("Sequence"))
        End If
        GetContainerScrapInfo = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function
    ''' <summary>
    ''' 获取已经有ERP反馈信息的报废单记录
    ''' add:Wangjh 20180425
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetErpRecvStepScrap(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSContainerInStoreIBusiness.GetErpRecvStepScrap
        Dim strSql As String = "SELECT csp.containerid,csp.workflowstepid,c.containername,ws.workflowstepname FROM containerstepscrapinfo csp" &
                            " LEFT JOIN container c ON c.containerid=csp.containerid" &
                            " LEFT JOIN workflowstep ws ON ws.workflowstepid=csp.workflowstepid" &
                            " WHERE csp.qmnum IS NOT NULL AND csp.zfpdbh IS NOT NULL "
        strSql += String.Format(" AND csp.containerid='{0}'", para("ContainerID"))
        If para.ContainsKey("WorkFlowID") Then
            strSql += String.Format(" AND ws.workflowid='{0}'", para("WorkFlowID"))
        End If
        If para.ContainsKey("Sequence") Then
            strSql += String.Format(" AND ws.sequence<={0}", para("Sequence"))
        End If
        GetErpRecvStepScrap = DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取ERP库存地点 add YangJiang 20180518
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetInventoryLocation() As System.Data.DataTable Implements IBusiness.uLMSContainerInStoreIBusiness.GetInventoryLocation
        Dim strSQL = "SELECT eil.erpinstorelocatiname||eil.description LGORT,eil.erpinstorelocatiname FROM ERPInStoreLocation eil "
        GetInventoryLocation = DatabaseHelper.Query_ReturnDatTable(strSQL)
    End Function
    ''' <summary>
    ''' 检查是否--有未解除的首件鉴定
    ''' add by longb 2022-07-29
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckFirstIdentifyProduct(ByVal para As Dictionary(Of String, String), Optional ByRef msg As String = "") As Boolean Implements IBusiness.uLMSContainerInStoreIBusiness.CheckFirstIdentifyProduct
        Dim sb As New StringBuilder
        sb.AppendLine(<string>SELECT f.firstidentifyproductid FROM FirstIdentifyProduct f 
        WHERE NVL(STATUS,0)='0' AND f.identifytype IN('1','2') AND f.drawingno||':'||f.versionno IN (
        SELECT pb.productname||':'|| p.productrevision FROM product p 
        LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid </string>)
        sb.AppendFormat(" WHERE pb.productname='{0}')", para("PRODUCTNAME"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim result As Boolean = dt.Rows.Count > 0
        If result Then
            DatabaseHelper.ExecuteSql(String.Format("update FirstIdentifyProduct set factoryid='{0}' where identifytype IN('1','2') and firstidentifyproductid IN({1})", para("FACTORYID"), sb.ToString))
            Dim message As String = "该件号有尚未完成的{0}，请联系分厂配置的控制点解除处理人员【{1}】在QMS系统中反馈。"
            Dim emplSQL As String = <string>SELECT wm_concat(e.FULLNAME) employeename from roledef ro 
				                    left JOIN employeerole erl ON erl.roleid=ro.roleid
				                    left JOIN employee e on e.employeeid=erl.employeeid
				                    LEFT JOIN SESSIONVALUES s ON s.SESSIONVALUESID =e.SESSIONVALUESID 
				                    where ro.rolename like '%控制点解除处理人员' AND s.FACTORYID='{0}'</string>
            Dim empl As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(emplSQL, para("FACTORYID")))
            Dim identifytype As String = String.Empty
            Dim typeDT As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format("SELECT f.identifytype FROM FirstIdentifyProduct f  where firstidentifyproductid in ({0})", sb.ToString))
            If typeDT.Select("identifytype='1'").Length > 0 Then
                identifytype += "首件鉴定"
            End If

            If typeDT.Select("identifytype='2'").Length > 0 Then
                identifytype += IIf(String.IsNullOrWhiteSpace(identifytype), "", "/") + "质量评审"
            End If

            If empl.Rows.Count > 0 Then
                msg = String.Format(message, identifytype, empl.Rows(0)("employeename").ToString)
            End If
        End If
        Return result
    End Function

    ''' <summary>
    ''' 获取已经入库申请的数据 add zhunan 2025.9.16
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetInstoreToWMSData(para As Dictionary(Of String, String)) As DTO.uMESProcessResult Implements IBusiness.uLMSContainerInStoreIBusiness.GetInstoreToWMSData
        Dim sql = <string>

                  </string>.Value
        Dim condition As String = String.Format(" and cfci.factoryid='{0}'", para("FactoryID"))
        If para.Keys.Contains("MfgOrderName") Then
            condition += String.Format(" and cci.mfgordername like '%{0}%'", para("MfgOrderName"))
        End If
        If para.Keys.Contains("ContainerName") Then
            condition += String.Format(" and cci.ContainerName like '%{0}%'", para("ContainerName"))
        End If
        If para.Keys.Contains("ProductName") Then
            condition += String.Format(" and cci.ProductName like '%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("SpecNO") Then
            condition += String.Format(" and s.SpecNO like '%{0}%'", para("SpecNO"))
        End If

        Dim strSql As String = <string>
SELECT cci.mfgorderid,c.mfgbatch,cci.productdescription description,ws.workflowstepname,s.specno,cci.mfgordername,SUM(c.qty) qty,
       cci.togetherflag,cci.productname,ws.workflowstepid,ws.workflowid,ws.sequence,c.containertype,
       DECODE(c.containertype,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS containertypename,
       SUM(cfci.acceptanceqty) acceptanceqty,SUM(cfci.lossqty) lossqty,SUM(cfci.defectqty) defectqty,SUM(cfci.reworkqty) reworkqty,
       (NVL(cci.workflowroute,cci.productname)||'/'||cci.containerno) productbatch,
       (cci.productname||'/'||cci.containerno) productbatchhidden 
FROM sync_sendto_wms wms
LEFT JOIN containerinstore cid ON cid.containerinstoreid = wms.uniquecolvalue
LEFT JOIN containercurrentinfo cci ON cci.containername = cid.charg
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN containerfinishedcheckinfo cfci ON cfci.containerid = cci.containerid AND cfci.isuse = 0
LEFT JOIN workflowstep ws ON ws.workflowstepid = cfci.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid= ws.specbaseid
LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
WHERE wms.ismeshandle = 1 AND wms.isuse = '0' AND wms.methodname = '入库申请' {0}
GROUP BY cci.mfgorderid,c.mfgbatch,cci.productdescription,ws.workflowstepname,s.specno,cci.mfgordername,
         cci.togetherflag,cci.productname,ws.workflowstepname,ws.workflowstepid,ws.workflowid,ws.sequence,c.containertype,
         cci.workflowroute,cci.productname,cci.containerno
                               </string>.Value

        strSql = String.Format(strSql, condition.ToString())

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        GetInstoreToWMSData = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function
End Class
